package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IScriptDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.ScriptCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.ScriptSearchDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.ScriptUpdateDAOException;
import com.che.software.testato.domain.entity.MapArrow;
import com.che.software.testato.domain.entity.Script;
import com.che.software.testato.domain.entity.Variant;
import com.che.software.testato.domain.entity.creation.ScriptCreation;
import com.che.software.testato.domain.entity.search.ScriptSearch;

/**
 * JDBC implementation of the DAO interface dedicated to the scripts management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IScriptDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("scriptDAO")
public class ScriptDAO extends AbstractDAO implements IScriptDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(ScriptDAO.class);

	/**
	 * Creates the scripts for a given hierarchy.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param hierarchyId the hierarchy id.
	 * @param scripts the scripts to create.
	 * @since July, 2011.
	 * @throws ScriptCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createScriptsFromHierarchy(int hierarchyId, List<ScriptCreation> scripts) throws ScriptCreationDAOException {
		LOGGER.debug("createScriptsFromHierarchy(" + hierarchyId + "," + scripts.size() + " scripts).");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			for (ScriptCreation script : scripts) {
				getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth) VALUES(nextval('script_seq'),?,'', ?) ", new Object[] { hierarchyId, 1 });
				Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId"));
				createItems(connection, script.getScriptArrows(), createdScript, 1, hierarchyId);
			}
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new ScriptCreationDAOException(e1);
			}
			throw new ScriptCreationDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Checks if some script have been elicited for a given hierarchy.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param hierarchyId the hierarchy id.
	 * @return true if the hierarchy has already been elicitate, else false.
	 * @since July, 2011.
	 * @throws ScriptSearchDAOException if an error occurs during the search.
	 */
	@Override
	public boolean isScriptExistingFromHierarchyId(int hierarchyId) throws ScriptSearchDAOException {
		LOGGER.debug("isScriptExistingFromHierarchyId(" + hierarchyId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT script_id FROM script WHERE hierarchy_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { hierarchyId });
		} catch (SQLException e) {
			throw new ScriptSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Recursive method. Creates the items for a given script. Then, for each
	 * item, makes a recursive call to create their children items. The stop
	 * condition is reached when variants are discovered in an item.
	 * 
	 * @param connection the connection to use.
	 * @param items the items to create.
	 * @param scriptId the parent script. Will be the same for all items.
	 * @param depth the level of the items to create. Default is one and it's
	 *        incremented at each iteration.
	 * @param hierarchyId the related hierarchy id.
	 * @throws SQLException if an SQLException occurs during the creations.
	 */
	private void createItems(Connection connection, List<MapArrow> items, int scriptId, int depth, int hierarchyId) throws SQLException {
		LOGGER.debug("createItems(" + items.size() + " items," + scriptId + "," + depth + ").");
		for (int i = 1; i <= items.size(); i++) {
			MapArrow item = items.get(i - 1);
			getQueryRunner().update(connection, "INSERT INTO script_item(script_item_id, script_id, \"ORDER\", label) VALUES(nextval('script_item_seq'),?,?,?) ", new Object[] { scriptId, i, "[" + item.getSourceLabel() + "," + item.getLabel() + "," + item.getTargetLabel() + "]" });
			Integer createdItem = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_item_id)::int AS scriptItemId FROM script_item ", new ScalarHandler("scriptItemId"));
			if (null != item.getScripts() && !item.getScripts().isEmpty()) {
				for (ScriptCreation itemScripts : item.getScripts()) {
					getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth, parent_script_item) VALUES(nextval('script_seq'), ?, '', ?, ?) ", new Object[] { hierarchyId, (depth + 1), createdItem });
					Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId"));
					createItems(connection, itemScripts.getScriptArrows(), createdScript, (depth + 1), hierarchyId);
				}
			} else { // Stop condition.
				for (int j = 1; j <= item.getVariants().size(); j++) {
					getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth, parent_script_item) VALUES(nextval('script_seq'), ?, '', ?, ?) ", new Object[] { hierarchyId, (depth + 1), createdItem });
					Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId"));
					Variant variant = item.getVariants().get(j - 1);
					getQueryRunner().update(connection, "INSERT INTO script_item(script_item_id, script_id, \"ORDER\", label) VALUES(nextval('script_item_seq'),?,?,?) ", new Object[] { createdScript, j, variant.getLabel() });
				}
			}
		}
	}

	/**
	 * Script search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws ScriptSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Script> searchScript(ScriptSearch searchBean) throws ScriptSearchDAOException {
		LOGGER.debug("searchScript().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getScriptSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Script>(Script.class), params.toArray());
		} catch (SQLException e) {
			throw new ScriptSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Updates a script from his object.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param scriptToUpdate the script object to update in the database.
	 * @since August, 2011.
	 * @throws ScriptUpdateDAOException if an error occurs during the update.
	 */
	@Override
	public void updateScript(Script scriptToUpdate) throws ScriptUpdateDAOException {
		LOGGER.debug("updateScript().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			getQueryRunner().update(connection, getScriptUpdateQueryFromCriterion(scriptToUpdate, params), params.toArray());
		} catch (SQLException e) {
			throw new ScriptUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Recovery of the script search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getScriptSearchQueryFromCriterion(ScriptSearch searchBean, List<Object> params) {
		LOGGER.debug("getScriptSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT DISTINCT(script_id) AS scriptId, hierarchy_id AS parentHierarchyId, label, depth, parent_script_item AS parentScriptItem FROM script ");
		if (null != searchBean && null != searchBean.getIterationId()) {
			sBuilder.append("JOIN comparisonMatrixItem ON(first_script = script_id) JOIN iteration_assignment USING(iteration_assignment_id) ");
		}
		if (null != searchBean && null != searchBean.getSelected()) {
			sBuilder.append("JOIN iteration_assignment_source_script USING(script_id) ");
		}
		if (null != searchBean && null != searchBean.getParentHierarchyId() && 0 != searchBean.getParentHierarchyId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("hierarchy_id = ? ");
			params.add(searchBean.getParentHierarchyId());
		}
		if (null != searchBean && null != searchBean.getDepth() && 0 != searchBean.getDepth()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("depth = ? ");
			params.add(searchBean.getDepth());
		}
		if (null != searchBean && null != searchBean.getParentScriptItem() && 0 != searchBean.getParentScriptItem()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("parent_script_item = ? ");
			params.add(searchBean.getParentScriptItem());
		}
		if (null != searchBean && null != searchBean.getParentScriptItem() && 0 == searchBean.getParentScriptItem()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("parent_script_item IS NULL ");
		}
		if (null != searchBean && null != searchBean.getIterationId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_id = ? ");
			params.add(searchBean.getIterationId());
		}
		if (null != searchBean && null != searchBean.getSelected()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("selected = ? ");
			params.add(searchBean.getSelected());
		}
		sBuilder.append("ORDER BY script_id ");
		return sBuilder.toString();
	}

	/**
	 * Recovery of the script update query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param scriptToUpdate the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getScriptUpdateQueryFromCriterion(Script scriptToUpdate, List<Object> params) {
		LOGGER.debug("getScriptUpdateQueryFromCriterion().");
		setSetClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("UPDATE script ");
		if (null != scriptToUpdate && null != scriptToUpdate.getLabel()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("label = ? ");
			params.add(scriptToUpdate.getLabel());
		}
		sBuilder.append("WHERE script_id = ? ");
		params.add(scriptToUpdate.getScriptId());
		return sBuilder.toString();
	}
}